﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Data;
using EnterpriseFund.Common;

namespace EnterpriseFund.Controllers
{
    public class AllowanceIssueSummarySheetController : Common.AdminBaseController
    {
        //
        // GET: /AllowanceIssueSummarySheet/

        public ActionResult Index()
        {
            return View();
        }

        public ActionResult JSummarySheet(string issueID)
        {
            BLL.AllowanceIssue allowanceIssueBLL = new BLL.AllowanceIssue();
            Model.AllowanceIssue a = allowanceIssueBLL.GetModelByIssueNo(issueID);
            BLL.WorkFlowProcess workFlowProcessBLL = new BLL.WorkFlowProcess();
           // List<Model.WorkFlowProcess> wL = workFlowProcessBLL.GetModelList("  FTableName ='t_AllowanceIssue' and FSourceNo= '" + a.FIssueID + "'");
            //List<Model.WorkFlowProcess> w = (from nm in wL where nm.FSeq == 10 select nm).ToList();


            if (a != null)
            {           
                ViewData["shr"] = workFlowProcessBLL.GetTaxCheckIssName(a.FIssueID);
                ViewData["fhr"] = workFlowProcessBLL.GetCHekIssName(a.FIssueID);
                BLL.AllowanceIssueEntry AllowanceIssueEntryBLL = new BLL.AllowanceIssueEntry();
                DataSet aleDs = AllowanceIssueEntryBLL.GetWCQKList("FIssueID='" + a.FIssueID + "'");
                if (aleDs.Tables[0].Rows.Count > 0)
                {
                    object sum = aleDs.Tables[0].Compute("Sum(counts)", "");
                    object sum1 = aleDs.Tables[0].Compute("Sum(counts)", "FStatus=0");
                    object sum2 = aleDs.Tables[0].Compute("Sum(counts)", "FStatus=2");
                    if (sum1 == null || sum1.ToString() == "")
                    {
                        sum1 = 0;
                    }
                    if (sum2 == null || sum2.ToString() == "")
                    {
                        sum2 = 0;
                    }
                    if (sum.ToString() == sum2.ToString())
                    {
                        ViewData["fhr"] = "徐晓莉";//需求修改后程序为设计审核人字段 写死；
                    }
                    else
                    {
                        ViewData["fhr"] = "";
                    }
                }

                ViewData["FIssueNo"] = a.FIssueNo;
                if (a.FAllowanceClassNo == "J")
                {
                    ViewData["showData"] = GetData(a.FIssueID, (a.FYear - 1).ToString(), "J");
                    return View("JSummarySheet");
                }
                else
                {
                    ViewData["showData"] = GetData(a.FIssueID, a.FYear.ToString(), "Y");
                    return View("YSummarySheet");
                }
            }
            else
            {
                return null;
            }
        }

        public string GetWCQK(string issueID)
        {
            BLL.AllowanceIssueEntry AllowanceIssueEntryBLL = new BLL.AllowanceIssueEntry();

            BLL.AllowanceIssue allowanceIssueBLL = new BLL.AllowanceIssue();
            
            Model.AllowanceIssue a = allowanceIssueBLL.GetModelByIssueNo(issueID);
            if (a != null)
            {
                DataSet aleDs = AllowanceIssueEntryBLL.GetWCQKList("FIssueID='" + a.FIssueID + "'");
                if (aleDs.Tables[0].Rows.Count > 0)
                {
                    object sum = aleDs.Tables[0].Compute("Sum(counts)", "");
                    object sum1 = aleDs.Tables[0].Compute("Sum(counts)", "FStatus=0");
                    object sum2 = aleDs.Tables[0].Compute("Sum(counts)", "FStatus=2");
                    if (sum1 == null || sum1.ToString() == "")
                    {
                        sum1 = 0;
                    }
                    if (sum2 == null || sum2.ToString() == "")
                    {
                        sum2 = 0;
                    }
                    return "总企业数：" + sum + " 已发送：" + (int.Parse(sum.ToString()) - int.Parse(sum1.ToString())).ToString() + "已复核：" + sum2;
                }
                else
                {
                    return "总企业数：0 已发送：0 已复核：0";
                }
            }
            else
            {
                return "";
            }
        }

        public DataTable GetNewData(string year, string type, List<Model.TaxClassAllowance> taxClassAllowanceList)
        {
            DataTable newDt = new DataTable();
            newDt.Columns.Add("xh");
            newDt.Columns.Add("nsrbm");
            newDt.Columns.Add("NSRMC");
            newDt.Columns.Add("FFinancePropName");


            string e = "";
            foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
            {
                newDt.Columns.Add(t.FTaxClassAllowanceNo + "e", typeof(decimal));
                if (e == "")
                {
                    e += "["+t.FTaxClassAllowanceNo + "e]";
                }
                else
                {
                    e += "+[" + t.FTaxClassAllowanceNo + "e]";
                }
            }
            newDt.Columns.Add("ygzhj", typeof(decimal));
            newDt.Columns.Add("ehj", typeof(decimal));
            if (e == "")
            {
                e += "0-[ygzhj]";
            }
            else
            {
                e += "-[ygzhj]";
            }
            newDt.Columns["ehj"].Expression = e;

            foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
            {
                if (type == "Y")
                {
                    newDt.Columns.Add(t.FTaxClassAllowanceNo + "l");
                }
                else
                {
                    if (t.FTaxClassAllowanceName.Contains("增值税"))
                    {
                        if (t.FTaxClassAllowanceName.Contains("营改"))
                        {
                            newDt.Columns.Add(t.FTaxClassAllowanceNo + "l");
                        }
                        else
                        {
                            newDt.Columns.Add(t.FTaxClassAllowanceNo + "base");
                            newDt.Columns.Add(t.FTaxClassAllowanceNo + "FInnerRate");
                            newDt.Columns.Add(t.FTaxClassAllowanceNo + "FOuterRate");
                        }
                    }
                    else
                    {
                        newDt.Columns.Add(t.FTaxClassAllowanceNo + "l");
                    }
                }
            }

            e = "";
            foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
            {
                newDt.Columns.Add(t.FTaxClassAllowanceNo + "k", typeof(decimal));
                if (e == "")
                {
                    e +="["+ t.FTaxClassAllowanceNo + "k]";
                }
                else
                {
                    e += "+[" + t.FTaxClassAllowanceNo + "k]";
                }
            }

            newDt.Columns.Add("khj", typeof(decimal));
            newDt.Columns["khj"].Expression = e;
            newDt.Columns.Add("ybtSum",typeof(decimal));

            e = "";
            foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
            {
                newDt.Columns.Add(t.FTaxClassAllowanceNo + "b", typeof(decimal));
                if (e == "")
                {
                    e +="["+ t.FTaxClassAllowanceNo + "b]";
                }
                else
                {
                    e += "+[" + t.FTaxClassAllowanceNo + "b]";
                }
            }
            newDt.Columns.Add("tstz", typeof(decimal));
            if (e == "")
            {
                e += "tstz";
            }
            else
            {
                e += "+[tstz]";
            }

            newDt.Columns.Add("bhj", typeof(decimal));
            newDt.Columns["bhj"].Expression = e;
            return newDt;
        }

        public DataTable GetData(string issueID, string year, string type)
        {
            BLL.TaxClassAllowance taxClassAllowanceBLL = new BLL.TaxClassAllowance();
            List<Model.TaxClassAllowance> taxClassAllowanceList = taxClassAllowanceBLL.GetModelList("FYear=" + year);
            DataTable newDt = GetNewData(year, type, taxClassAllowanceList);
            ViewData["taxClassAllowanceList"] = taxClassAllowanceList;
            BLL.TaxAuditPolicyEval taxAuditPolicyEvalBLL = new BLL.TaxAuditPolicyEval();
            DataSet aIeDs = taxAuditPolicyEvalBLL.GetAllowanceIssueEntryListByFIssueID(issueID);
            DataSet tAeDs = taxAuditPolicyEvalBLL.GetTaxAuditEntryListByFIssueID(issueID);
            DataSet tPeDs = taxAuditPolicyEvalBLL.GetTaxAuditPolicyEvalListByFIssueID(issueID);
            BLL.SpecificAdjust specificAdjustBLL = new BLL.SpecificAdjust();
            List<Model.SpecificAdjust> specificAdjustList = specificAdjustBLL.GetModelList(" a.FIssueID ='" + issueID + "'");
            foreach (Model.SpecificAdjust s in specificAdjustList)
            {
                s.FAdjustAmount = s.FAdjustAmount / 10000;
            }
            if (tPeDs.Tables.Count > 0)
            {
                DataTable tPsDt = tPeDs.Tables[0];
                DataTable tAeDt = tAeDs.Tables[0];
                DataTable aIeDt = aIeDs.Tables[0];

                for (int z = 0; z < tAeDt.Rows.Count; z++)
                {
                    tAeDt.Rows[z]["FAdjustAmount"] = decimal.Round(decimal.Parse(tAeDt.Rows[z]["FAdjustAmount"].ToString()), 0);
                }

                for (int z = 0; z < tPsDt.Rows.Count; z++)
                {
                    if (tPsDt.Rows[z]["FBaseAmount"] != null && tPsDt.Rows[z]["FBaseAmount"].ToString() != "")
                    {
                        tPsDt.Rows[z]["FBaseAmount"] = decimal.Round(decimal.Parse(tPsDt.Rows[z]["FBaseAmount"].ToString()), 0);
                    }
                    if (tPsDt.Rows[z]["FInnerRate"] != null && tPsDt.Rows[z]["FInnerRate"].ToString() != "")
                    {
                        tPsDt.Rows[z]["FInnerRate"] = decimal.Round(decimal.Parse(tPsDt.Rows[z]["FInnerRate"].ToString()) * 100, 2);

                    }
                    if (tPsDt.Rows[z]["FOuterRate"] != null && tPsDt.Rows[z]["FOuterRate"].ToString() != "")
                    {
                        tPsDt.Rows[z]["FOuterRate"] = decimal.Round(decimal.Parse(tPsDt.Rows[z]["FOuterRate"].ToString()) * 100, 2);
                    }
                }

                for (int z = 0; z < aIeDt.Rows.Count; z++)
                {
                    aIeDt.Rows[z]["FAllowancePeriodAmt"] = decimal.Round(decimal.Parse(aIeDt.Rows[z]["FAllowancePeriodAmt"].ToString()) / 10000, 0);
                    aIeDt.Rows[z]["FAllowanceBalAmt"] = decimal.Round(decimal.Parse(aIeDt.Rows[z]["FAllowanceBalAmt"].ToString()) / 10000, 0);
                    if (aIeDt.Rows[z]["FFactAllowanceAmt"] != null && aIeDt.Rows[z]["FFactAllowanceAmt"].ToString() != "")
                    {
                        aIeDt.Rows[z]["FFactAllowanceAmt"] = decimal.Round(decimal.Parse(aIeDt.Rows[z]["FFactAllowanceAmt"].ToString()) / 10000, 2);
                    }
                    else
                    {
                        aIeDt.Rows[z]["FFactAllowanceAmt"] = 0;
                    }
                }

                var q = (from nm in tPsDt.AsEnumerable()
                         group nm by
                             new
                             {
                                 t1 = nm.Field<decimal>("NSRDZDAH"),
                                 t2 = nm.Field<string>("NSRMC"),
                                 t3 = nm.Field<string>("FFinancePropNo"),
                                 t4 = nm.Field<string>("FFinancePropName"),
                                 t6 = nm.Field<string>("nsrbm")
                             } into g
                         select new
                         {
                             NSRDZDAH = g.Key.t1,
                             NSRMC = g.Key.t2,
                             FFinancePropNo = g.Key.t3,
                             FFinancePropName = g.Key.t4,
                             nsrbm = g.Key.t6
                         });
                int i = 0;
                q = q.OrderBy(c => c.nsrbm);
                foreach (var n in q)
                {
                    i++;
                    DataRow ndr = newDt.NewRow();
                    string FTaxAuditIds = "";
                    List<string> FFinancePropNos = new List<string>();
                    var q2 = (from nm in tPsDt.AsEnumerable() where nm.Field<decimal>("NSRDZDAH") == n.NSRDZDAH select nm);
                    foreach (var tn in q2)
                    {
                        if (tn["FTaxAuditId"].ToString() != "-1")
                        {
                            FTaxAuditIds = tn["FTaxAuditId"].ToString();
                        }
                        if (n.FFinancePropNo == tn["FFinancePropNo"].ToString())
                        {
                            FFinancePropNos.Add(tn["FTaxClassAllowanceNo"].ToString());
                        }
                    }
                    ndr["xh"] = i;
                    ndr["NSRMC"] = n.NSRMC;
                    ndr["nsrbm"] = n.nsrbm;
                    ndr["FFinancePropName"] = n.FFinancePropName;

                    foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
                    {
                        if (n.FFinancePropNo == "04" || n.FFinancePropNo == "07")
                        {
                            if (!FFinancePropNos.Contains(t.FTaxClassAllowanceNo))
                            {
                                ndr[t.FTaxClassAllowanceNo + "e"] = "0";
                                continue;
                            }
                        }
                        else
                        {
                            if (t.FTaxClassAllowanceNo == "05")
                            {
                                ndr[t.FTaxClassAllowanceNo + "e"] = "0";
                                continue;
                            }
                        }

                        var s = (from nm in tAeDt.AsEnumerable() where nm.Field<string>("FTaxAuditId") == FTaxAuditIds && nm.Field<string>("FTaxClassAllowanceNo") == t.FTaxClassAllowanceNo select nm);

                        if (s.Count() > 0)
                        {
                            ndr[t.FTaxClassAllowanceNo + "e"] = s.Sum(z => z.Field<decimal>("FAdjustAmount"));
                        }
                        else
                        {
                            ndr[t.FTaxClassAllowanceNo + "e"] = "0";
                        }

                    }

                    ndr["ygzhj"] = 0;

                    List<string> tNo = new List<string>();
                    foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
                    {
                        //if (tNo.Contains(t.FTaxClassAllowanceNo))
                        //{
                        var s = (from nm in tPsDt.AsEnumerable() where nm.Field<string>("FTaxAuditId") == FTaxAuditIds && nm.Field<string>("FTaxClassAllowanceNo") == t.FTaxClassAllowanceNo && nm.Field<string>("FFinancePropNo") == n.FFinancePropNo select nm);
                        if (s.Count() > 0)
                        {

                            DataTable sdt = s.CopyToDataTable();

                            tNo.Add(sdt.Rows[0]["FTaxClassAllowanceNo"].ToString());
                            if (type == "Y")
                            {
                                ndr[t.FTaxClassAllowanceNo + "l"] = sdt.Rows[0]["FOuterRate"];
                            }
                            else
                            {
                                if (t.FTaxClassAllowanceName.Contains("增值税"))
                                {
                                    if (t.FTaxClassAllowanceName.Contains("营改"))
                                    {
                                        ndr[t.FTaxClassAllowanceNo + "l"] = decimal.Parse(sdt.Rows[0]["FOuterRate"].ToString()).ToString("G0");
                                    }
                                    else
                                    {
                                        ndr[t.FTaxClassAllowanceNo + "base"] = sdt.Rows[0]["FBaseAmount"];
                                        ndr[t.FTaxClassAllowanceNo + "FInnerRate"] = decimal.Parse(sdt.Rows[0]["FInnerRate"].ToString()).ToString("G0");
                                        ndr[t.FTaxClassAllowanceNo + "FOuterRate"] = decimal.Parse(sdt.Rows[0]["FOuterRate"].ToString()).ToString("G0");
                                    }
                                }
                                else
                                {
                                    ndr[t.FTaxClassAllowanceNo + "l"] = decimal.Parse(sdt.Rows[0]["FOuterRate"].ToString()).ToString("G0");
                                }
                            }
                        }
                        else
                        {
                            if (type == "Y")
                            {
                                ndr[t.FTaxClassAllowanceNo + "l"] = "0";
                            }
                            else
                            {
                                if (t.FTaxClassAllowanceName.Contains("增值税"))
                                {
                                    if (t.FTaxClassAllowanceName.Contains("营改"))
                                    {
                                        ndr[t.FTaxClassAllowanceNo + "l"] = "";
                                    }
                                    else
                                    {
                                        ndr[t.FTaxClassAllowanceNo + "base"] = "0";
                                        ndr[t.FTaxClassAllowanceNo + "FInnerRate"] = "0";
                                        ndr[t.FTaxClassAllowanceNo + "FOuterRate"] = "0";
                                    }
                                }
                                else
                                {
                                    ndr[t.FTaxClassAllowanceNo + "l"] = "0";
                                }
                            }
                        }
                        //}
                        //else
                        //{
                        //    if (type == "Y")
                        //    {
                        //        ndr[t.FTaxClassAllowanceNo + "l"] = "0";
                        //    }
                        //    else
                        //    {
                        //        if (t.FTaxClassAllowanceName.Contains("增值税"))
                        //        {
                        //            ndr[t.FTaxClassAllowanceNo + "base"] = "0";
                        //            ndr[t.FTaxClassAllowanceNo + "FInnerRate"] = "0";
                        //            ndr[t.FTaxClassAllowanceNo + "FOuterRate"] = "0";
                        //        }
                        //        else
                        //        {
                        //            ndr[t.FTaxClassAllowanceNo + "l"] = "0";
                        //        }
                        //    }
                        //}
                    }

                    foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
                    {
                        if (tNo.Contains(t.FTaxClassAllowanceNo))
                        {
                            var s = (from nm in aIeDt.AsEnumerable() where nm.Field<decimal>("NSRDZDAH") == n.NSRDZDAH && nm.Field<string>("FTaxClassAllowanceNo") == t.FTaxClassAllowanceNo select nm);
                            if (s.Count() > 0)
                            {
                                DataTable sdt = s.CopyToDataTable();
                                ndr[t.FTaxClassAllowanceNo + "k"] = sdt.Rows[0]["FAllowanceBalAmt"];
                            }
                            else
                            {
                                ndr[t.FTaxClassAllowanceNo + "k"] = "0";
                            }
                        }
                        else
                        {
                            ndr[t.FTaxClassAllowanceNo + "k"] = "0";
                        }
                    }

                    var y = (from nm in aIeDt.AsEnumerable() where nm.Field<decimal>("NSRDZDAH") == n.NSRDZDAH && tNo.Contains(nm.Field<string>("FTaxClassAllowanceNo")) select nm);
                    if (y.Count() > 0)
                    {
                        ndr["ybtSum"] = y.Sum(z => z.Field<decimal>("FAllowancePeriodAmt"));
                    }
                    else
                    {
                        ndr["ybtSum"] = "0";
                    }

                    foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
                    {
                        if (tNo.Contains(t.FTaxClassAllowanceNo))
                        {
                            var s = (from nm in aIeDt.AsEnumerable() where nm.Field<decimal>("NSRDZDAH") == n.NSRDZDAH && nm.Field<string>("FTaxClassAllowanceNo") == t.FTaxClassAllowanceNo select nm);
                            if (s.Count() > 0)
                            {
                                DataTable sdt = s.CopyToDataTable();
                                ndr[t.FTaxClassAllowanceNo + "b"] = decimal.Round(decimal.Parse(sdt.Rows[0]["FFactAllowanceAmt"].ToString()), 0);
                            }
                            else
                            {
                                ndr[t.FTaxClassAllowanceNo + "b"] = "0";
                            }
                        }
                        else
                        {
                            ndr[t.FTaxClassAllowanceNo + "b"] = "0";
                        }

                    }

                    List<Model.SpecificAdjust> nSpecificAdjustList = (from nm in specificAdjustList where nm.NSRDZDAH == n.NSRDZDAH select nm).ToList();
                    if (nSpecificAdjustList.Count > 0)
                    {
                        ndr["tstz"] = nSpecificAdjustList.Sum(ns => ns.FAdjustAmount);
                    }
                    else
                    {
                        ndr["tstz"] = "0";
                    }

                    newDt.Rows.Add(ndr);
                }


                DataRow hjdr = newDt.NewRow();
                foreach (DataColumn dc in newDt.Columns)
                {
                    if (dc.DataType == typeof(decimal))
                    {
                        hjdr[dc.ColumnName] = newDt.Compute("sum([" + dc.ColumnName + "])", "");
                    }
                    if (dc.ColumnName == "xh")
                    {
                        hjdr["xh"] = "合计";
                    }

                    if (dc.ColumnName == "NSRMC")
                    {
                        var qn = (from nm in tPsDt.AsEnumerable()
                                  group nm by
                                      new
                                      {
                                          t1 = nm.Field<decimal>("NSRDZDAH")
                                      } into g
                                  select new
                                  {
                                      NSRDZDAH = g.Key.t1
                                  });
                        hjdr["NSRBM"] = "共" + qn.Count() + "户";
                    }
                }
                newDt.Rows.Add(hjdr);
            }


            //foreach (DataRow dr in newDt.Rows)
            //{
            //    foreach (DataColumn dc in newDt.Columns)
            //    {
            //        decimal temp = 0;
            //        if (decimal.TryParse(dr[dc].ToString(),out temp))
            //        {
            //            if (temp == 0)
            //            {
            //                //dr[dc] = DBNull.Value;
            //            }
            //        }
            //    }
            //}
            return newDt;
        }
        
        public System.Web.Mvc.FileResult ToExecl()
        {
            string issNo = Fetch.Post("hidissueID");
            BLL.AllowanceIssue allowanceIssueBLL = new BLL.AllowanceIssue();
            Model.AllowanceIssue a = allowanceIssueBLL.GetModelByIssueNo(issNo);
            DataTable dt = new DataTable();
            if (a != null)
            {
                if (a.FAllowanceClassNo == "J")
                {
                    dt = GetData(a.FIssueID, (a.FYear - 1).ToString(), "J");
                }
                else
                {
                    dt = GetData(a.FIssueID, a.FYear.ToString(), "Y");
                }

                BLL.TaxClassAllowance taxClassAllowanceBLL = new BLL.TaxClassAllowance();
                List<Model.TaxClassAllowance> taxClassAllowanceList = taxClassAllowanceBLL.GetModelList("FYear=" + a.FYear.ToString());

                int totalRowCount = 2;
                List<Model.XlsModel> xlsModelList = new List<Model.XlsModel>();

                int colCount = 4;

                Model.XlsModel xlsModel = new Model.XlsModel();
                xlsModel.RowSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "序号";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;

                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.RowSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "纳税编码";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.RowSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "企业名称";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.RowSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "资金项目";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = taxClassAllowanceList.Count+2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "本年实绩";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                colCount += taxClassAllowanceList.Count;

                if (a.FAllowanceClassNo == "J")
                {
                    foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
                    {
                        if (t.FTaxClassAllowanceName.Contains("增值税"))
                        {
                            if (t.FTaxClassAllowanceName.Contains("营改"))
                            {
                                xlsModel = new Model.XlsModel();
                                xlsModel.RowSpan = 2;
                                xlsModel.RowIndex = totalRowCount;
                                xlsModel.Title = t.FTaxClassAllowanceName + "补贴率";
                                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                                xlsModelList.Add(xlsModel);
                                colCount += 1;
                            }
                            else
                            {
                                xlsModel = new Model.XlsModel();
                                xlsModel.ColSpan = 3;
                                xlsModel.RowIndex = totalRowCount;
                                xlsModel.Title = t.FTaxClassAllowanceName;
                                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                                xlsModelList.Add(xlsModel);
                                colCount += 3;
                            }

                        }
                        else
                        {
                            xlsModel = new Model.XlsModel();
                            xlsModel.RowSpan = 2;
                            xlsModel.RowIndex = totalRowCount;
                            xlsModel.Title = t.FTaxClassAllowanceName + "补贴率";
                            xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                            xlsModelList.Add(xlsModel);
                            colCount += 1;
                        }
                    }
                }
                else
                {
                    foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
                    {
                        xlsModel = new Model.XlsModel();
                        xlsModel.RowSpan = 2;
                        xlsModel.RowIndex = totalRowCount;
                        xlsModel.Title = t.FTaxClassAllowanceName + "补贴率";
                        xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                        xlsModelList.Add(xlsModel);
                        colCount += 1;
                    }
                }

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = taxClassAllowanceList.Count+1;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "可补贴金额";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                colCount += taxClassAllowanceList.Count;

                xlsModel = new Model.XlsModel();
                xlsModel.RowSpan = 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "已补贴金额";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                colCount += 1;
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = taxClassAllowanceList.Count + 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "本期补贴金额";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);
                colCount += taxClassAllowanceList.Count;

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = colCount;
                xlsModel.RowIndex = 1;
                xlsModel.BottomLineStyle = 0;
                xlsModel.RightLineStyle = 0;
                xlsModel.LeftLineStyle = 0;
                xlsModel.TopLineStyle = 0;
                xlsModel.Title = "制表日期：" + DateTime.Now.ToString("yyyy年MM月dd日");
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = 4;
                xlsModel.RowIndex = 1;
                xlsModel.BottomLineStyle = 0;
                xlsModel.RightLineStyle = 0;
                xlsModel.LeftLineStyle = 0;
                xlsModel.TopLineStyle = 0;
                xlsModel.Title = "（税收金额单位为元  补贴金额单位为万元）";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                totalRowCount++;

                foreach (Model.TaxClassAllowance t in taxClassAllowanceList)
                {
                    xlsModel = new Model.XlsModel();
                    xlsModel.RowIndex = totalRowCount;
                    xlsModel.Title = t.FTaxClassAllowanceName;
                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                    xlsModelList.Add(xlsModel);
                }

                xlsModel = new Model.XlsModel();
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "营改增补贴";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "合计";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                if (a.FAllowanceClassNo == "J")
                {
                    xlsModel = new Model.XlsModel();
                    xlsModel.RowIndex = totalRowCount;
                    xlsModel.Title = "基数";
                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                    xlsModelList.Add(xlsModel);
                    xlsModel = new Model.XlsModel();
                    xlsModel.RowIndex = totalRowCount;
                    xlsModel.Title = "基数内补贴率";
                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                    xlsModelList.Add(xlsModel);
                    xlsModel = new Model.XlsModel();
                    xlsModel.RowIndex = totalRowCount;
                    xlsModel.Title = "基数外补贴率(补贴率)";
                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                    xlsModelList.Add(xlsModel);
                }

                foreach (EnterpriseFund.Model.TaxClassAllowance t in taxClassAllowanceList)
                {
                    xlsModel = new Model.XlsModel();
                    xlsModel.RowIndex = totalRowCount;
                    xlsModel.Title = t.FTaxClassAllowanceName;
                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                    xlsModelList.Add(xlsModel);
                }

                xlsModel = new Model.XlsModel();
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "小计";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                foreach (EnterpriseFund.Model.TaxClassAllowance t in taxClassAllowanceList)
                {
                    xlsModel = new Model.XlsModel();
                    xlsModel.RowIndex = totalRowCount;
                    xlsModel.Title = t.FTaxClassAllowanceName;
                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                    xlsModelList.Add(xlsModel);
                }

                xlsModel = new Model.XlsModel();
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "特殊调整";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.RowIndex = totalRowCount;
                xlsModel.Title = "小计";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);


                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    totalRowCount++;
                    foreach (System.Data.DataColumn dc in dt.Columns)
                    {
                        decimal d = 0;
                        if (decimal.TryParse(dt.Rows[i][dc.ColumnName].ToString(), out d))
                        {
                            if (d != 0)
                            {
                                xlsModel = new Model.XlsModel();
                                xlsModel.RowIndex = totalRowCount;
                                if (dc.ColumnName == "xh")
                                {
                                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                                }
                                else
                                {
                                    xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Right;
                                }
                                xlsModel.Title = dt.Rows[i][dc.ColumnName].ToString();
                                xlsModelList.Add(xlsModel);
                            }
                            else
                            {
                                xlsModel = new Model.XlsModel();
                                xlsModel.RowIndex = totalRowCount;
                                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Right;
                                xlsModel.Title = "";
                                xlsModelList.Add(xlsModel);
                            }
                        }
                        else
                        {
                            xlsModel = new Model.XlsModel();
                            xlsModel.RowIndex = totalRowCount;
                            xlsModel.Title = dt.Rows[i][dc.ColumnName].ToString();
                            if (dc.ColumnName == "NSRMC")
                            {
                                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Left;
                            }
                            else
                            {
                                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                            }
                            xlsModelList.Add(xlsModel);
                        }
                    }
                }

                BLL.WorkFlowProcess workFlowProcessBLL = new BLL.WorkFlowProcess();
                string shr = workFlowProcessBLL.GetTaxCheckIssName(a.FIssueID);
                string fhr = workFlowProcessBLL.GetCHekIssName(a.FIssueID); ;
                BLL.AllowanceIssueEntry AllowanceIssueEntryBLL = new BLL.AllowanceIssueEntry();
                DataSet aleDs = AllowanceIssueEntryBLL.GetWCQKList("FIssueID='" + a.FIssueID + "'");
                if (aleDs.Tables[0].Rows.Count > 0)
                {
                    object sum = aleDs.Tables[0].Compute("Sum(counts)", "");
                    object sum1 = aleDs.Tables[0].Compute("Sum(counts)", "FStatus=0");
                    object sum2 = aleDs.Tables[0].Compute("Sum(counts)", "FStatus=2");
                    if (sum1 == null || sum1.ToString() == "")
                    {
                        sum1 = 0;
                    }
                    if (sum2 == null || sum2.ToString() == "")
                    {
                        sum2 = 0;
                    }
                    if (sum.ToString() == sum2.ToString())
                    {
                        fhr = "徐晓莉";//需求修改后程序为设计审核人字段 写死；
                    }
                    else
                    {
                        fhr = "";
                    }
                }
                totalRowCount++;
                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = colCount / 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.BottomLineStyle = 0;
                xlsModel.RightLineStyle = 0;
                xlsModel.LeftLineStyle = 0;
                xlsModel.TopLineStyle = 0;
                xlsModel.Title = "财监处审核：" + shr + "											                                                        	";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                xlsModel = new Model.XlsModel();
                xlsModel.ColSpan = colCount / 2;
                xlsModel.RowIndex = totalRowCount;
                xlsModel.BottomLineStyle = 0;
                xlsModel.RightLineStyle = 0;
                xlsModel.LeftLineStyle = 0;
                xlsModel.TopLineStyle = 0;
                xlsModel.Title = "预算处审核：" + fhr + "												";
                xlsModel.HAlign = Model.XlsModel.HorizontalAlign.Center;
                xlsModelList.Add(xlsModel);

                List<Model.XlsModel_Column> xlsModelColumns = new List<Model.XlsModel_Column>();
                xlsModelColumns.Add(new Model.XlsModel_Column()
                {
                    ColumnIndexStart = 0,
                    ColumnIndexEnd = 30,
                    Width = 10
                });

                List<Model.XlsModel_Row> xlsModelRows = new List<Model.XlsModel_Row>();
                ushort rowCount = (ushort)(totalRowCount);
                xlsModelRows.Add(new Model.XlsModel_Row()
                {
                    RowIndexStart = 1,
                    RowIndexEnd = 1,
                    RowHeight = 23
                });

                xlsModelRows.Add(new Model.XlsModel_Row()
                {
                    RowIndexStart = 2,
                    RowIndexEnd = 7,
                    RowHeight = 12

                });
                xlsModelRows.Add(new Model.XlsModel_Row()
                {
                    RowIndexStart = 8,
                    RowIndexEnd = rowCount,
                    RowHeight = 15

                });

                Model.ExcelTitleFont excelTitleFont = new Model.ExcelTitleFont()
                {
                    FontName = "宋体",
                    FontSize = 18,
                    Bold = true
                };

                byte[] b = Common.ExcelClass.ExportToExcelReturnByte("", "企业扶持资金审核汇总表 " + a.FIssueNo, "企业扶持资金审核汇总表" + a.FIssueNo, excelTitleFont, totalRowCount, xlsModelList, xlsModelColumns, xlsModelRows);

                string name = "企业扶持资金审核汇总表" + a.FIssueNo +".xls";
                return File(b, "application/vnd.ms-excel", HttpUtility.UrlEncode(name));
            }
            else
            {
                return null;
            }
        }

        public ActionResult YSummarySheet(string issueID)
        {
            BLL.AllowanceIssue allowanceIssueBLL = new BLL.AllowanceIssue();
            Model.AllowanceIssue a = allowanceIssueBLL.GetModel(issueID);
            ViewData["showData"] = GetData(issueID, a.FYear.ToString(), "Y");
            return View("YSummarySheet");
        }
    }
}
